kmore

kmore

A Knex little more typed factory of SQL query builder powed by TypeScript, with auto-generated type-safe tables accessor for Node.js".

Installation

npm install kmore knex

# Then add one of the following:
npm install pg
npm install mssql
npm install oracle
npm install sqlite3

Usage

Build configuration:

Ensure sourceMap or inlineSourceMap is true in the tsconfig.json

{
  "compilerOptions": {
    "sourceMap": true
  },
}

Create connection

import { Config, TTables } from 'kmore'

// connection config
export const config: Config = {
  client: 'pg',
  // connection: process.env.PG_CONNECTION_STRING,
  connection: {
    host: 'localhost',
    user: 'postgres',
    password: 'foo',
    database: 'db_ci_test',
  },
}

// Define Types of tables
export interface TbListModel extends TTables {
  tb_user: User
  tb_user_detail: UserDetail
}

export interface User {
  uid: number
  name: string
  ctime: Date | 'now()'
}
export interface UserDetail {
  uid: number
  age: number
  address: string
}  

/**
 *  Initialize db connection and generate type-safe tables accessor (name and builder)
 *  will get
 *    - db.dbh : the connection instance of knex
 *        eg. db.dbh<OtherType>('tb_other').select()
 *    - db.tables : tables name accessor containing table key/value paris
 *    - db.columns : table column names accessor containing table key/value paris
 *    - db.scopedColumns : table column names accessor containing table key/value paris, with table prefix
 *    - db.rb : tables builder accessor,
 *        eg. db.rb.user() =>  Knex.QueryBuilder<{id: number, name: string}>
 *  tables will be generated from generics automaitically when passing undefined or null value
 */
const db = kmore<TbListModel>({ config })
// or
const kTables = genTbListFromType<TbListModel>()
const db = kmore<TbListModel>({ config }, kTables)

Create tables with instance of knex

await db.dbh.schema
  .createTable('tb_user', (tb) => {
    tb.increments('uid')
    tb.string('name', 30)
    tb.timestamp('ctime', { useTz: false })
  })
  .createTable('tb_user_detail', (tb) => {
    tb.integer('uid')
    tb.foreign('uid')
      .references('tb_user.uid')
      .onDelete('CASCADE')
      .onUpdate('CASCADE')
    tb.integer('age')
    tb.string('address', 255)
  })
  .catch((err: Error) => {
    assert(false, err.message)
  })

Inert rows via auto generated table accessor

// auto generated accessort tb_user() and tb_user_detail() on db.rb
const { tb_user, tb_user_detail } = db.rb

await tb_user()
  .insert([
    { name: 'user1', ctime: new Date() }, // ms
    { name: 'user2', ctime: 'now()' }, // μs
  ])
  .then()

await tb_user_detail()
  .insert([
    { uid: 1, age: 10, address: 'address1' },
    { uid: 2, age: 10, address: 'address1' },
  ])
  .returning('*')
  .then()

Join tables

const { tables: t, rb, scopedColumns: sc } = db

await rb.tb_user<UserDetail>()
  .select()
  .innerJoin(
    t.tb_user_detail,
    sc.tb_user.uid,
    sc.tb_user_detail.uid,
  )
  .where(sc.tb_user.uid, 1)
  .then((rows) => {
    const [row] = rows
    assert(row && row.uid)
    assert(row && row.name)
    assert(row && row.age)
    return rows
  })

Use instance of knex

// drop table
await db.dbh.raw(`DROP TABLE IF EXISTS "${tb}" CASCADE;`).then()

// disconnect
await db.dbh.destroy()

Demo

• see test

Packages

kmore is comprised of many specialized packages. This repository contains all these packages. Below you will find a summary of each package.

Package	Version	Dependencies	DevDependencies
kmore
kmore-types
kmore-cli
egg-kmore

License

MIT

Languages

• English
• 中文

Changelog

0.21.0 (2020-06-17)

Code Refactoring

• generation of scopedCols (4852406)

BREAKING CHANGES

• • remove type BaseMultiTableColumns, use MultiTableCols instead

• remove type MultiTableScopedCols, use MultiTableCols instead